﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace zVendorControl
{
    public partial class ucBomVendor : UserControl
    {
        public ucBomVendor()
        {
            InitializeComponent();
            LoadGrid();
            loaddataMain("");
        }

        #region 加载产品清单
        /// <summary>
        /// 加载产品清单
        /// </summary>
        private void loaddataMain(string value)
        {
            using (var cn = Common.handleSQL.getConnect())
            {
                var rnd = new Random();
                var tmptable = "##t" + rnd.Next(10000, 99999);
                var cmd = cn.CreateCommand();
                var sql = String.Format("if(object_id('tempdb..{0}') is not null) drop table {0}", tmptable);
                cmd.ExecSQL(sql);
                sql = @"select bom_bom.BomId,bom_bom.BomType,bom_bom.Version,bom_parent.ParentId,
inventory.cInvCode,inventory.cInvName,inventory.cInvStd,bom_bom.VersionDesc,
bom_bom.Define1,bom_bom.Define2,bom_bom.Define3,bom_bom.Define4,bom_bom.Define5,
bom_bom.Define6,bom_bom.Define7,bom_bom.Define8,bom_bom.Define9,bom_bom.Define10,
bom_bom.VersionEffDate,bom_bom.VersionEndDate
 into  " + tmptable + 
@" from bom_bom inner join bom_parent on bom_bom.BomId=bom_parent.BomId 
 left join bas_part on bom_parent.ParentId=bas_part.PartId 
 left join Inventory on bas_part.InvCode=Inventory.cInvCode 
 where inventory.cInvCode like @val or inventory.cInvName like @val or inventory.cInvStd like @val
order by inventory.cInvCode";
                cmd.ExecSQL(sql, new System.Data.SqlClient.SqlParameter("@val", "%" + value + "%"));
                string strSum;
                var strCols = Common.cDataGrid.GetColumnsStr("BomMain",out strSum);
                sql = "select " + strCols + " from " + tmptable + "  order by cInvCode ";
                var dt_data = cmd.GetData(sql);
                //sql = "select " + strSum + " from " + tmptable;
                //var dt_Sum = cmd.GetData(sql);
                sql = String.Format("if(object_id('tempdb..{0}') is not null) drop table {0}", tmptable);
                cmd.ExecSQL(sql);

                int iRowSum = dt_data.Rows.Count;
                this.dgmain.RowCount = iRowSum ;
                for (int i = 0; i < dt_data.Rows.Count; i++)
                {
                    this.dgmain.Rows[i].SetValues(dt_data.Rows[i].ItemArray);
                }
                //this.dgmain.Rows[iRowSum].SetValues(dt_Sum.Rows[0].ItemArray);
                //this.dgmain.Rows[iRowSum].DefaultCellStyle.ForeColor = Color.Blue;
                #region 如果只有一行，则加载清单明细
                if (iRowSum == 1)
                {
                    var bomid = this.dgmain.Rows[0].Cells["BomId"].Value.GetVal_Int();
                    if (bomid != 0)
                    { loaddataDetail(bomid); }
                }
                #endregion
            }
        }
        #endregion


        #region 加载BOM表明细
        /// <summary>
        /// 加载BOM表明细
        /// </summary>
        private void loaddataDetail(int bomid)
        {
            using (var cn = Common.handleSQL.getConnect())
            {
                var rnd = new Random();
                var tmptable = "##t" + rnd.Next(10000, 99999);
                var cmd = cn.CreateCommand();
                var sql = String.Format("if(object_id('tempdb..{0}') is not null) drop table {0}", tmptable);
                cmd.ExecSQL(sql);
                sql = string.Format(@"select BomId,SortSeq,OpSeq,ComponentId,
inventory.cInvCode,inventory.cInvName,inventory.cInvStd,
(select cVenName+'('+ case when DATEDIFF(d,GETDATE(),bom_opcomponent_vendor.expDate)>=1 and DATEDIFF(d,GETDATE(),bom_opcomponent_vendor.startDate)<=0 
 then '有效' else '无效' end +'),' from bom_opcomponent_vendor inner join Vendor on bom_opcomponent_vendor.cVenCode=vendor.cVenCode
  where  bom_opcomponent_vendor.BomId=bom_opcomponent.BomId and bom_opcomponent.ComponentId=bom_opcomponent_vendor.ComponentId
 for xml path('')) as cVenName,BaseQtyN,BaseQtyD
into  " + tmptable +
@"  from bom_opcomponent
 left join bas_part on bom_opcomponent.ComponentId=bas_part.PartId 
 left join Inventory on bas_part.InvCode=Inventory.cInvCode 
 where bom_opcomponent.BomId={0}  order by inventory.cInvCode", bomid);
                cmd.ExecSQL(sql);
                string strSum;
                var strCols = Common.cDataGrid.GetColumnsStr("BomDetail", out strSum);
                sql = "select " + strCols + " from " + tmptable + " order by cInvCode ";
                var dt_data = cmd.GetData(sql);
                sql = "select " + strSum + " from " + tmptable;
                var dt_Sum = cmd.GetData(sql);
                sql = String.Format("if(object_id('tempdb..{0}') is not null) drop table {0}", tmptable);
                cmd.ExecSQL(sql);
                int iRowSum = dt_data.Rows.Count;
                this.dgdetail.RowCount = iRowSum ;
                for (int i = 0; i < dt_data.Rows.Count; i++)
                {
                    this.dgdetail.Rows[i].SetValues(dt_data.Rows[i].ItemArray);
                }
                this.dgdetail.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
                this.dgdetail.AutoResizeRows();
                //this.dgdetail.Rows[iRowSum].SetValues(dt_Sum.Rows[0].ItemArray);
                //this.dgdetail.Rows[iRowSum].DefaultCellStyle.ForeColor = Color.Blue;
            }
        }
        #endregion


        private void LoadGrid()
        {
            Common.cDataGrid.initGrid("BomMain", this.dgmain);
            Common.cDataGrid.initGrid("BomDetail", this.dgdetail);
            this.dgdetail.ReadOnly = false;
            if (dgdetail.Columns.Contains("cVenName"))
            { dgdetail.Columns["cVenName"].Tag = "editstring"; }
        }

        private void dgmain_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            var bomid = this.dgmain.CurrentRow.Cells["BomId"].Value.GetVal_Int();
            if (bomid != 0)
            { loaddataDetail(bomid); }
        }

        private void dgdetail_bt_Ref_Click(object sender, EventArgs e)
        {
            var cr = this.dgdetail.CurrentRow;
            var ComponentId = cr.Cells["ComponentId"].Value.GetVal_Int();
            var bomid = cr.Cells["BomId"].Value.GetVal_Int();
            if (bomid != 0 && ComponentId != 0)
            {
                var f = new FrmVenSet();
                f.LoadData(bomid, ComponentId);
                f.ShowDialog(this);
                this.dgdetail.EndEdit();
                loaddataDetail(bomid);
            }
        }

        #region 查找按钮
        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            loaddataMain(this.toolStripTextBox1.Text.Trim());
        }

        private void toolStripTextBox1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                toolStripButton1_Click(sender,e);
            }
        }
        #endregion

        #region 导出Excel
        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            try
            {
                this.toolStrip1.Enabled = false;
                var sql = @"select mother.cInvCode,mother.cInvName,mother.cInvStd,bom_bom.Version,bom_bom.VersionDesc,
child.cInvCode as cInvCode_C,child.cInvName as cInvName_C,child.cInvStd as cInvStd_C,
bom_opcomponent_vendor.cVenCode,vendor.cVenName,bom_opcomponent_vendor.startDate,bom_opcomponent_vendor.expDate,
bom_opcomponent_vendor.cMaker,bom_opcomponent_vendor.dDate,
case when ISNULL(bom_opcomponent_vendor.cVenCode,'')='' then '' else  
case when DATEDIFF(d,GETDATE(),bom_opcomponent_vendor.expDate)>=1 and DATEDIFF(d,GETDATE(),bom_opcomponent_vendor.startDate)<=0 
 then '有效' else '无效' end end as [desc]
 from bom_bom left join bom_parent on bom_bom.BomId=bom_parent.BomId 
left join bom_opcomponent on bom_bom.BomId=bom_opcomponent.BomId 
left join bom_opcomponent_vendor on (bom_opcomponent.BomId=bom_opcomponent_vendor.BomId and bom_opcomponent.ComponentId=bom_opcomponent_vendor.ComponentId)
left join bas_part  motherpart on bom_parent.ParentId=motherpart.PartId 
left join Inventory mother on motherpart.InvCode=mother.cInvCode 
left join bas_part childpart on bom_opcomponent.ComponentId=childpart.PartId 
left join Inventory child on childpart.InvCode=child.cInvCode
left join Vendor on bom_opcomponent_vendor.cVenCode=vendor.cVenCode 
order by mother.cInvCode,bom_bom.Version,child.cInvCode,bom_opcomponent_vendor.cVenCode ";
                var dt = Common.handleSQL.getdata(sql);
                var dgv = new DataGridView();
                Common.cDataGrid.initGrid("bomExp", dgv);
                dgv.RowCount = dt.Rows.Count;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dgv.Rows[i].SetValues(dt.Rows[i].ItemArray);
                }
                string filename = DateTime.Now.ToString("yyyy-MM-dd") + "备案供应商数据";
                Common.cDataGrid.exportExcel(dgv,filename);
            }
            catch (Exception ex)
            {
                MessageBox.Show("出现错误:" + ex.Message + Environment.NewLine + Environment.NewLine + ex.StackTrace);
            }
            finally { this.toolStrip1.Enabled = true; }
        }
        #endregion
    }
}
